﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;

namespace DAO
{
    public class CT_SanBay_DAO
    {
        #region Kiem tra du lieu va cac quy dinh
        //ham kiem tra
        public static bool kiemTraCTSB(string maCB, string maSB)
        {
            string query = "select * from ChiTietSB where macb = '" + maCB + "' and masb = '" + maSB + "'";
            DataTable ctsb_tab = SqlDataAccessHelper.ExecuteQuery(query);
            if (ctsb_tab.Rows.Count == 0)
                return false;
            return true;
        }

        public static bool kiemTraCTSB(string maCB)
        {
            string query = "select * from ChiTietSB where macb = '" + maCB;
            DataTable ctsb_tab = SqlDataAccessHelper.ExecuteQuery(query);
            if (ctsb_tab == null)
                return false;
            return true;
        }

        //ham kiem tra so sb trung gian

        //da du so sbtrung gian thi tra ve true
        //khong thi false
        public static bool kiemTraSoSBTrungGian(string maCB)
        {
            QuyDinh_DTO quyDinh = QuyDinh_DAO.layQuyDinh();
            string query = "select count(MaSB) SoLuong from chitietsb where macb = '" + maCB + "'";
            DataTable ctsb_tab = SqlDataAccessHelper.ExecuteQuery(query);
            if (int.Parse(ctsb_tab.Select()[0]["SoLuong"].ToString()) >= quyDinh.soSBTrungGianMax)
                return true;
            return false;
        }
        //ham kiem tra thoi gian dung
        public static bool kiemTraTGianDung(int thoiGianDung)
        {
            QuyDinh_DTO quyDinh = QuyDinh_DAO.layQuyDinh();
            if (thoiGianDung >= quyDinh.thoiGianDungMin && thoiGianDung <= quyDinh.thoiGianDungMax)
                return true;
            return false;
        }
        #endregion 

        #region Truy van du lieu
        //Cac ham truy xuat du lieu
        public static List<CT_SanBay_DTO> layDanhSachCTSanBay()
        {
            string query = "select * from chitietsb";
            DataTable ctsb_tab = SqlDataAccessHelper.ExecuteQuery(query);
            if (ctsb_tab == null)
                return null;
            List<CT_SanBay_DTO> ctsb_list = new List<CT_SanBay_DTO>();
            for (int i = 0; i < ctsb_tab.Rows.Count; i++)
            {
                ctsb_list.Add(new CT_SanBay_DTO());
                ctsb_list[i].maCB = ctsb_tab.Select()[i]["MaCB"].ToString();
                ctsb_list[i].maSB = ctsb_tab.Select()[i]["MaSB"].ToString();
                ctsb_list[i].loaiSB = int.Parse(ctsb_tab.Select()[i]["Loai"].ToString());
                ctsb_list[i].tGianDung = int.Parse(ctsb_tab.Select()[i]["ThoiGianDung"].ToString());
            }
            return ctsb_list;
        }
        public static List<CT_SanBay_DTO> layCTSanBayTheoMaChuyenBay(String maCB)
        {
            string query = "select * from chitietsb where macb = '" + maCB + "'";
            DataTable ctsb_tab = SqlDataAccessHelper.ExecuteQuery(query);
            if (ctsb_tab == null)
                return null;
            List<CT_SanBay_DTO> ctsb_list = new List<CT_SanBay_DTO>();
            for (int i = 0; i < ctsb_tab.Rows.Count; i++)
            {
                ctsb_list.Add(new CT_SanBay_DTO());
                ctsb_list[i].maCB = ctsb_tab.Select()[i]["MaCB"].ToString();
                ctsb_list[i].maSB = ctsb_tab.Select()[i]["MaSB"].ToString();
                ctsb_list[i].loaiSB = int.Parse(ctsb_tab.Select()[i]["Loai"].ToString());
                ctsb_list[i].tGianDung = int.Parse(ctsb_tab.Select()[i]["ThoiGianDung"].ToString());
            }
            return ctsb_list;
        }
     

        public static List<CT_SanBay_DTO> layCTSanBayTheoLoaiSanBay(int loaiSB)
        {
            string query = "select * from chitietsb where loaiSB =  " + loaiSB.ToString();
            DataTable ctsb_tab = SqlDataAccessHelper.ExecuteQuery(query);
            if (ctsb_tab == null)
                return null;
            List<CT_SanBay_DTO> ctsb_list = new List<CT_SanBay_DTO>();
            for (int i = 0; i < ctsb_tab.Rows.Count; i++)
            {
                ctsb_list.Add(new CT_SanBay_DTO());
                ctsb_list[i].maCB = ctsb_tab.Select()[i]["MaCB"].ToString();
                ctsb_list[i].maSB = ctsb_tab.Select()[i]["MaSB"].ToString();
                ctsb_list[i].loaiSB = int.Parse(ctsb_tab.Select()[i]["Loai"].ToString());
                ctsb_list[i].tGianDung = int.Parse(ctsb_tab.Select()[i]["ThoiGianDung"].ToString());
            }
            return ctsb_list;
        }

        #endregion
        //1-sbdi
        //2-sbden
        //3-sb trung gian

        #region Thao tac insert, update, delete
        //Cac ham tuong tac voi co so du lieu
        public static bool themCTSanBay(CT_SanBay_DTO ctSanBay)    //them 1 bang chi tiet san bay
        {
            string query = "insert into ChiTietSB(masb, macb, loai, thoigiandung) "
                            + "values ('" + (ctSanBay.maSB + "', '") + (ctSanBay.maCB + "', '") + (ctSanBay.loaiSB.ToString() + "', '") + (ctSanBay.tGianDung.ToString() + "' ) ");
            int res = SqlDataAccessHelper.ExecuteNoneQuery(query);
            if (res != 0)
                return true;
            return false;
        }
        public static bool xoaCTSanBay(CT_SanBay_DTO ctSanBay)     //xoa 1 bang chi tiet san bay
        {
            string query = "delete ChiTietSB where masb = '" + ctSanBay.maSB + "' and macb = '" + ctSanBay.maCB + "'";
            int res = SqlDataAccessHelper.ExecuteNoneQuery(query);
            if (res != 0)
                return true;
            return false;
        }
        public static bool suaCTSanBay(CT_SanBay_DTO ctSanBay)     //sua 1 bang chi tiet san bay
        {
            string query = "Update ChiTietSB "
                            + "set " + ("macb = '" + ctSanBay.maCB + "', ") + ("masb = '" + ctSanBay.maSB + "', ")
                            + ("loai = " + ctSanBay.loaiSB.ToString() + ", ") + ("thoigiandung = " + ctSanBay.tGianDung.ToString())
                            + " where macb = '" + ctSanBay.maCB + "' and masb = '" + ctSanBay.maSB + "'";
            int res = SqlDataAccessHelper.ExecuteNoneQuery(query);
            if (res != 0)
                return true;
            return false;
        }

        #endregion
    }
}
